using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;

public class DBAccess
{
    private SqlCommand com;
    private SqlDataAdapter da;
    private SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ConnectionString);

    public DBAccess()
    {

    }

    public void openConnection()
    {
        try
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
                con.Open();
            }
            else
            {
                con.Open();
            }
        }
        catch (Exception ex)
        {
            throw ex;
        } 
    }

    public void closeConnection()
    {
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
    }

      
    public int execute(string spName, SqlParameter[] paraList)
    {
        int result = 0;
        openConnection();
        com = new SqlCommand(spName, con);
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.AddRange(paraList);
        result = com.ExecuteNonQuery();
        closeConnection();
        return result;
    }

    public DataTable executeSelect(string spName)
    {
        openConnection();
        DataTable dt = new DataTable();
        da = new SqlDataAdapter(spName, con);
        da.SelectCommand.CommandType = CommandType.StoredProcedure;     
        da.Fill(dt);
        closeConnection();
        return dt;
    }


    public DataTable executeSelect(string spName,SqlParameter[] paraList)
    {
        openConnection();
        DataTable dt = new DataTable();
        da = new SqlDataAdapter(spName, con);
        da.SelectCommand.CommandType = CommandType.StoredProcedure;
        da.SelectCommand.Parameters.AddRange(paraList);
        da.Fill(dt);
        closeConnection();
        return dt;
    }

    public int executeScalary(string spName, SqlParameter[] paraList)
    {
        int result = 0;
        openConnection();
        com= new SqlCommand(spName, con);
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.AddRange(paraList);
        result = int.Parse(com.ExecuteScalar().ToString());
        closeConnection();
        return result;
    }

}
